package com.rongzhong.routing.check;

import java.io.File;
import java.util.Date;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONObject;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.rongzhong.routing.checkNode.DataNode;
import com.rongzhong.routing.hibernate.HibernateUtil;
import com.rongzhong.routing.main.RoutingInspecte;
import com.rongzhong.routing.util.Configuration;
import com.rongzhong.routing.util.PerformanceUtil;

/**
 * 
 * @author longxuegang@gmail.com
 * @version 1.0 2011-9-22
 * @since 1.0
 * @see
 */

public class DataCheck {
	private Configuration configure = Configuration.getInstance();
	private HibernateUtil hibernate = HibernateUtil.getInstance();
	private PerformanceUtil perfUtil = PerformanceUtil.getInstance();
	private final String table_schema = configure.getTableSchema();
	private final String sourcefile = configure.getExcel();
	private final int dbCheckNumber = perfUtil.getDbCheckNumber(); //3
	private static final Logger log = LoggerFactory.getLogger(DataCheck.class);
	
	public Map<Integer, Map<String,String>> getValuesMap(){
		return RoutingInspecte.getInstance().getValuesMap();
	}
	
	public void checkOutData(){ 
		for (int dbIndex = 0; dbIndex < dbCheckNumber; dbIndex++) {
			if (perfUtil.isMaster(dbIndex)) {
				DataNode bean = initDataNode(dbIndex);
				Session session = null;
				Transaction tr =  null;
				try {
					session = hibernate.getStorageDBSession();
					tr = session.beginTransaction();
					session.saveOrUpdate(bean);
					operateExcel(bean);
					tr.commit();
				} catch (Exception e) {
					tr.rollback();
					log.error("initBytes run into error.",e);
				}finally{
					hibernate.closeSession(session);
				}
			}
		}
	}
	
	public Object selectID(DataNode bean){
		Session session = null;
		try {
			session = hibernate.getStorageDBSession();
			SQLQuery query = session.createSQLQuery("select ID from DATACHECK where DATE= ? and HOST= ?");
			query.setDate(0, bean.getDate());
			query.setString(1, bean.getHost());
			return query.uniqueResult();
		} catch (Exception e) {
			log.error("initBytes run into error.",e);
			return null;
		}finally{
			hibernate.closeSession(session);
		}
	}
	
	private DataNode initDataNode(int dbIndex) {
		DataNode bean = new DataNode();
		bean.setDate(new Date(System.currentTimeMillis()));
		bean.setHost(perfUtil.getDBHostPort(dbIndex));
		int databaseCount = getDatabaseCount(dbIndex);
		int tableCount = getTableCount(dbIndex);
		double dataSizeAll = getDataSizeAll(dbIndex);
		double dataSizeAllFormat =  dataSizeAll/(1024*1024);
		bean.setDatabaseCount(databaseCount);
		bean.setTableCount(tableCount);
		bean.setDataSizeAll(Math.round(dataSizeAll));
		bean.setDataSizeAllFormat(new Double(perfUtil.doubleFormat("0.00",dataSizeAllFormat))); //单位MB
		
		
		double indexSizeAll = getIndexSizeAll(dbIndex);
		double indexSizeAllFormat = indexSizeAll/(1024*1024);
		bean.setIndexSizeAll(Math.round(indexSizeAll));
		bean.setIndexSizeAllFormat(new Double(perfUtil.doubleFormat("0.00",indexSizeAllFormat)));//单位MB
		
		
		boolean engine = isAllInnodb(dbIndex);
		boolean character = isAllUtf8(dbIndex);
		bean.setEngine(engine);
		bean.setCharacter(character);
		
		JSONObject maxData= getMaxDataLength(dbIndex);
		double maxDataLength = maxData.getDouble("DATA_LENGTH");
		double maxDataLengthFormat = maxDataLength/(1024*1024);
		double maxDataForIndexLength = maxData.getDouble("INDEX_LENGTH");
		double maxDataForIndexLengthFormat = maxDataForIndexLength/(1024*1024);
		String maxDataForTableName = maxData.getString("TABLE_NAME")+"\n" + maxData.getString("TABLE_COMMENT");
		bean.setMaxDataLength(Math.round(maxDataLength));
		bean.setMaxDataLengthFormat(new Double(perfUtil.doubleFormat("0.00",maxDataLengthFormat)));//单位MB
		bean.setMaxDataForIndexLength(Math.round(maxDataForIndexLength));
		bean.setMaxDataForIndexLengthFormat(new Double(perfUtil.doubleFormat("0.00",maxDataForIndexLengthFormat)));//单位MB
		bean.setMaxDataForTableName(maxDataForTableName);
		
		JSONObject maxIndex= getMaxIndexLength(dbIndex);
		double maxIndexLength = maxIndex.getDouble("INDEX_LENGTH");
		double maxIndexLengthFormat = maxIndexLength/(1024*1024);
		double maxIndexForDataLength = maxIndex.getDouble("DATA_LENGTH");
		double maxIndexForDataLengthFormat = maxIndexForDataLength/(1024*1024);
		String maxIndForTableName = maxIndex.getString("TABLE_NAME")+"\n" + maxIndex.getString("TABLE_COMMENT");
		bean.setMaxIndexLength(Math.round(maxIndexLength));
		bean.setMaxIndexLengthFormat(new Double(perfUtil.doubleFormat("0.00",maxIndexLengthFormat)));//单位MB
		bean.setMaxIndexForDataLength(Math.round(maxIndexForDataLength));
		bean.setMaxIndexForDataLengthFormat(new Double(perfUtil.doubleFormat("0.00",maxIndexForDataLengthFormat)));//单位MB
		bean.setMaxIndForTableName(maxIndForTableName);
		
		
		JSONObject maxTable= getMaxTableRows(dbIndex);
		int maxTableRows = maxTable.getInt("TABLE_ROWS");
		String maxTabForTableName = maxTable.getString("TABLE_NAME")+"\n" + maxIndex.getString("TABLE_COMMENT");
		bean.setMaxTableRows(maxTableRows);
		bean.setMaxTabForTableName(maxTabForTableName);
		
		JSONObject maxAvgRow= getMaxAvgRowLength(dbIndex);
		double maxAvgRowLength = maxAvgRow.getDouble("AVG_ROW_LENGTH");
		double maxAvgRowLengthFormat = maxAvgRowLength/1024;
		String maxAvgForTableName = maxAvgRow.getString("TABLE_NAME")+"\n" + maxAvgRow.getString("TABLE_COMMENT");
		bean.setMaxAvgRowLength(Math.round(maxAvgRowLength));
		bean.setMaxAvgRowLengthFormat(new Double(perfUtil.doubleFormat("0.00", maxAvgRowLengthFormat)));//单位KB
		bean.setMaxAvgForTableName(maxAvgForTableName);
		Object ID = selectID(bean);
		bean.setId(ID == null ? 0 : new Integer(ID.toString()));
		return bean;
	}
	
	public int getDatabaseCount(int dbIndex){
		String sql = "select count(distinct(TABLE_SCHEMA)) from information_schema.TABLES";
		String result = perfUtil.select(dbIndex, sql).toString();
		return new Integer(result);
	}
	
	public int getTableCount(int dbIndex){
		String sql = "select count(distinct(TABLE_NAME)) from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "'";
		String result = perfUtil.select(dbIndex, sql).toString();
		return new Integer(result);
	}
	
	public long getDataSizeAll(int dbIndex){
		String sql = "select sum(DATA_LENGTH) as 'Data Size' from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "'";
		Object result = perfUtil.select(dbIndex, sql);
		return result == null ? 0 : new Long(result.toString());
	}
	
	public long getIndexSizeAll(int dbIndex){
		String sql = "select sum(INDEX_LENGTH) as 'Index Size' from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "'";
		Object result = perfUtil.select(dbIndex, sql);
		return result == null ? 0 : new Long(result.toString());
	}
	
	public boolean isAllInnodb(int dbIndex){
		String sql = "select count(*) from information_schema.TABLES where ENGINE != 'InnoDB' and TABLE_SCHEMA = '" + table_schema + "'";
		String result = perfUtil.select(dbIndex, sql).toString();
		return new Integer(result) <= 0 ? true : false;
	}
	
	public boolean isAllUtf8(int dbIndex){
		String sql = "select count(*) from information_schema.TABLES where TABLE_COLLATION != 'utf8_general_ci' and TABLE_SCHEMA = '" + table_schema + "'";
		String result = perfUtil.select(dbIndex, sql).toString();
		return new Integer(result) <= 0 ? true : false;
	}
	
	public JSONObject getMaxDataLength(int dbIndex){
		String sql = "select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "' order by DATA_LENGTH desc limit 1 ";
		Object result = perfUtil.selectArray(dbIndex, sql);
		return perfUtil.initJson(new String[]{"TABLE_NAME","DATA_LENGTH","INDEX_LENGTH","TABLE_COMMENT"},(Object[])result);
	}
	
	public JSONObject getMaxIndexLength(int dbIndex){
		String sql = "select TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "' order by INDEX_LENGTH desc limit 1 ";
		Object result = perfUtil.selectArray(dbIndex, sql);
		return perfUtil.initJson(new String[]{"TABLE_NAME","DATA_LENGTH","INDEX_LENGTH","TABLE_COMMENT"},(Object[])result);
	}
	
	public JSONObject getMaxTableRows(int dbIndex){
		String sql = "select TABLE_NAME,TABLE_ROWS,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "' order by TABLE_ROWS desc limit 1 ";
		Object result = perfUtil.selectArray(dbIndex, sql);
		return perfUtil.initJson(new String[]{"TABLE_NAME","TABLE_ROWS","TABLE_COMMENT"},(Object[])result);
	}
	
	public JSONObject getMaxAvgRowLength(int dbIndex){
		String sql = "select TABLE_NAME,AVG_ROW_LENGTH,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA = '" + table_schema + "' order by AVG_ROW_LENGTH desc limit 1 ";
		Object result = perfUtil.selectArray(dbIndex, sql);
		return perfUtil.initJson(new String[]{"TABLE_NAME","AVG_ROW_LENGTH","TABLE_COMMENT"},(Object[])result);
	}
	
	private void operateExcel(DataNode bean) throws Exception{
		Workbook workBook = null;
		WritableWorkbook writeWorkBook = null;
		try {
			workBook = Workbook.getWorkbook(new File(sourcefile));
			writeWorkBook = Workbook.createWorkbook(new File(sourcefile), workBook);
			WritableSheet wsheet = writeWorkBook.getSheet(bean.getHost());
			int startColumnId = perfUtil.getStartPositionColumnId(wsheet,perfUtil.getLastReviewDate());
			int startRowId = perfUtil.getStartPositionRowId(wsheet,bean.getTaskNumber());
			Label label_0 = new Label(startColumnId, startRowId, bean.getDatabaseCount()+ "个");
			Label label_1 = new Label(startColumnId, startRowId + 1, bean.getTableCount()+ "个");
			Label label_2 = new Label(startColumnId, startRowId + 2, bean.getDataSizeAllFormat() + "MB");
			Label label_3 = new Label(startColumnId, startRowId + 3, bean.getIndexSizeAllFormat() + "MB");
			Label label_4 = new Label(startColumnId, startRowId + 4, bean.isEngine()? "正常" :"异常");
			Label label_5 = new Label(startColumnId, startRowId + 5, bean.isCharacter()? "正常" :"异常");
			Label label_6 = new Label(startColumnId, startRowId + 6, bean.getMaxDataLengthFormat() + "MB/" + bean.getMaxDataForIndexLengthFormat() + "MB");
			Label label_7 = new Label(startColumnId, startRowId + 7, bean.getMaxDataForTableName());
			Label label_8 = new Label(startColumnId, startRowId + 8, bean.getMaxIndexLengthFormat() + "MB/" + bean.getMaxIndexForDataLengthFormat() + "MB");
			Label label_9 = new Label(startColumnId, startRowId + 9, bean.getMaxIndForTableName());
			Label label_10 = new Label(startColumnId, startRowId + 10, bean.getMaxTableRows() + "行");
			Label label_11 = new Label(startColumnId, startRowId + 11, bean.getMaxTabForTableName());
			Label label_12 = new Label(startColumnId, startRowId + 12, bean.getMaxAvgRowLengthFormat() + "KB");
			Label label_13 = new Label(startColumnId, startRowId + 13, bean.getMaxAvgForTableName());
			wsheet.addCell(label_0);wsheet.addCell(label_1);wsheet.addCell(label_2);wsheet.addCell(label_3);
			wsheet.addCell(label_4);wsheet.addCell(label_5);wsheet.addCell(label_6);wsheet.addCell(label_7);
			wsheet.addCell(label_8);wsheet.addCell(label_9);wsheet.addCell(label_10);wsheet.addCell(label_11);
			wsheet.addCell(label_12);wsheet.addCell(label_13);
			writeWorkBook.write();
		} catch (Exception e) {
			log.error("operate excel run into error", e);
			throw new Exception(e);
		}finally{
			try {
				writeWorkBook.close();workBook.close();
			} catch (Exception e) {
				log.error("close WorkBook run into error",e);
				throw new Exception(e);
			}
		}
	}
}
